﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace template_dll
{
    public class SqlStorProc
    {
        public struct sp_params
        {
            public string name;
            public object value;
        }

        public int SqlProcExecNonQuery(SqlConnection con, string procname, int timeout, sp_params[] param)
        {
            int res = -1;
            try
            {
                if (con.State == ConnectionState.Closed) con.Open();
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (sp_params p in param)
                    {
                        com.Parameters.AddWithValue(p.name, p.value);
                    }
                }
                res = com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return res;
        }

        public DataTable[] SqlProcExecWithQuery(SqlConnection con, string procname, int timeout, sp_params[] param)
        {
            DataTable[] dt = new DataTable[0];
            try
            {
                if (con.State == ConnectionState.Closed) con.Open();
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (sp_params p in param)
                    {
                        com.Parameters.AddWithValue(p.name, p.value);
                    }
                }
                SqlDataReader dr = com.ExecuteReader();

                while (dr.HasRows)
                {
                    Array.Resize(ref dt, dt.Length + 1);
                    dt[dt.Length - 1] = new DataTable();
                    dt[dt.Length - 1].Load(dr);
                    if (dt[dt.Length - 1].Columns.Count > 2) dt[dt.Length - 1].TableName = dt[dt.Length - 1].Rows[0][1].ToString();
                    if (dr.IsClosed) break;
                }
                dr.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }

        public DataTable SqlProcExecWithQueryOneTable(SqlConnection con, string procname, int timeout, sp_params[] param)
        {
            DataTable dt = new DataTable();
            try
            {
                if (con.State == ConnectionState.Closed) con.Open();
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (sp_params p in param)
                    {
                        com.Parameters.AddWithValue(p.name, p.value);
                    }
                }
                dt.Load(com.ExecuteReader());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }
    }
}
